using System;
using System.Data.SqlClient;
using System.Collections;

namespace SQScriptRunner
{
	// Delegate declaration.
	//
	// The delegate must have the same signature as the method
	// you want to call asynchronously.
	public delegate string AsyncDelegate(String DatabaseConnectionString);

	/// <summary>
	/// Summary description for ScriptRunner.
	/// </summary>
	public class ScriptRunner
	{
		private ArrayList mSQLScripts = new ArrayList();

		public ScriptRunner()
		{
		}

		public ScriptRunner(String ScriptText)
		{
			AddScripts(ScriptText);
		}

		public ScriptRunner(System.IO.FileInfo ScriptFile)
		{
			AddScripts(ScriptFile);
		}

		public bool AppendScript(String ScriptText)
		{
			return AddScripts(ScriptText);
		}

		public bool AppendScript(System.IO.FileInfo ScriptFile)
		{
			return AddScripts(ScriptFile);
		}

		public string RunAgainst(String DatabaseConnectionString)
		{
			//build a connection
			String results = "";
			SqlConnection cn = new SqlConnection(DatabaseConnectionString);

			try
			{
				//open it
				cn.Open();

				SqlCommand cmd = new SqlCommand();

				cmd.CommandType = System.Data.CommandType.Text;
				//todo - config drive the timeout as appropriate
				cmd.CommandTimeout = 60 * 60;
				cmd.Connection = cn;

				//for each script in the list
				foreach (string sqlScript in mSQLScripts)
				{
					cmd.CommandText = sqlScript.Trim();
					if (cmd.CommandText.Length > 0)
					{
						int rowCount = cmd.ExecuteNonQuery();
						if (rowCount >= 0)
						{
							results += rowCount + " Records Affected";
						}
						else
						{
							results += "The command completed successfully.";
						}
					}
				}
			}
			catch(System.Exception ex)
			{
				throw(ex);
			}
			finally
			{
				if (cn !=  null && cn.State != System.Data.ConnectionState.Closed)
					cn.Close();
			}

			return results;
		}

		public bool TedsOriginal()
		{

			string strDatabaseConnectionString = (string)(new System.Configuration.AppSettingsReader()).GetValue("DatabaseConnectionString", typeof(string));
			System.Data.SqlClient.SqlConnection dbConnection = new System.Data.SqlClient.SqlConnection();
			try
			{
				Console.WriteLine("Establishing a database connection...");
				dbConnection.ConnectionString = strDatabaseConnectionString;
				dbConnection.Open();

				System.Data.SqlClient.SqlCommand dbCommand = new System.Data.SqlClient.SqlCommand();
				dbCommand.CommandType = System.Data.CommandType.Text;
				dbCommand.CommandTimeout = 240;
				dbCommand.Connection = dbConnection;

				string strFileDirectory = System.IO.Directory.GetCurrentDirectory();
				string[] astrFileNames = System.IO.Directory.GetFiles(strFileDirectory, "*.sql");
				Console.WriteLine("There are {0} script files to process.", astrFileNames.Length);
				foreach (string strFileName in astrFileNames)
				{
					if (System.IO.File.Exists(strFileName))
					{
						Console.WriteLine("Execute scripts in {0}", strFileName);
						using (System.IO.StreamReader srInputFile = System.IO.File.OpenText(strFileName))
						{
							string strFileText = srInputFile.ReadToEnd();
							string[] astrSqlScript = System.Text.RegularExpressions.Regex.Split(strFileText,@"\sGO\s");
							foreach (string strSqlScript in astrSqlScript)
							{
								dbCommand.CommandText = strSqlScript.Trim();
								if (dbCommand.CommandText.Length > 0)
								{
									int intRowCount = dbCommand.ExecuteNonQuery();
									if (intRowCount >= 0)
										Console.WriteLine("{0} Records Affected", intRowCount);
									else
										Console.WriteLine("The command completed successfully.");
								}
							}
						}
					}
				}
			}
			catch(System.Exception ex)
			{
				Console.WriteLine(ex.Message);
			}
			finally
			{
				if (dbConnection !=  null && dbConnection.State != System.Data.ConnectionState.Closed)
					dbConnection.Close();
			}
			Console.WriteLine("Press enter to exit.");
			string strReadLine = Console.ReadLine();

			return true;
		}

		private bool AddScripts(System.IO.FileInfo ScriptFile)
		{
			//split up the script into individual batches
			using (System.IO.StreamReader sr = ScriptFile.OpenText())
			{
				string fileText = sr.ReadToEnd();

				//whatever text is in the file, attempt to add any scripts from it
				AddScripts(fileText);				
			}
			return true;
		}

		private bool AddScripts(String ScriptText)
		{
			//if the string ends with the 'go' command
			if (ScriptText.EndsWith("GO"))
			{
				//strip it off
				ScriptText = ScriptText.Substring(0,ScriptText.Length-2);
			}

			//split the file on the GO command
			string[] sqlScripts;
			//should the reg exp require a new line before and after
			sqlScripts = System.Text.RegularExpressions.Regex.Split(ScriptText,@"\sGO\s");

			foreach (string sqlScript in sqlScripts)
			{
				mSQLScripts.Add(sqlScript);
			}

			return true;
		}
	}
}
